# -*- coding: utf-8 -*-

import pandas as pd
#import sqlite3
import datetime
import smtplib  
from email.mime.text import MIMEText
from email.mime import multipart
import os
import io
import tushare as ts
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
 
def getTag(percent):
    if percent>=7:
        return 7
    elif (percent>=6 and percent<7):
        return 6
    elif (percent>=5 and percent<6):
        return 5
    elif (percent>=4 and percent<5):
        return 4
    elif (percent>=3 and percent<4):
        return 3
    elif (percent>=2 and percent<3):
        return 2
    elif (percent>=1 and percent<2):
        return 1
    elif (percent>=0 and percent<1):
        return 0
    elif (percent>=-1 and percent<0):
        return -1
    elif (percent>=-2 and percent<-1):
        return -2
    elif (percent>=-3 and percent<-2):
        return -3
    elif (percent>=-4 and percent<-3):
        return -4
    elif (percent>=-5 and percent<-4):
        return -5
    elif (percent>=-6 and percent<-5):
        return -6
    else :
        return -7
def real_time_pic(img='temp01.svg'):
    nowdata=ts.get_today_all()
    nowdata['tag']=nowdata['changepercent'].apply(lambda x:getTag(x))
    tj=pd.DataFrame(nowdata.groupby('tag')['tag'].count())
    tj_data=pd.DataFrame([-7, -6, -5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5, 6, 7],columns=['tag'])
    tj_data.index=['un6','un5','un4','un3','un2','un1','un0','up0','up1','up2','up3','up4','up5','up6','up7']
    tj['td_count']=tj['tag'].cumsum()
    tj.columns=['tagnum','td_count']
    tj_data=tj_data.merge(tj,how='left',left_on='tag',right_index=True)
    tj_data['percent']=tj_data['tagnum'].cumsum()*1.0/tj_data['tagnum'].sum()
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False
    plt.figure(figsize=(12,6))
    tj_data['tagnum'].plot(kind='bar',grid=True)
    tj_data['percent'].plot(color='r',secondary_y=True,style='-o',linewidth=1,grid=True)
    i=0
    for x,y in tj_data['percent'].iteritems():
        plt.annotate(format(y,'.1%'), xy=(i, y), xytext=(i, y+0.1),
                     arrowprops=dict(arrowstyle='->',connectionstyle='arc3,rad=.1'))
        i=i+1
    plt.savefig(img,bbox_inches = 'tight')
    return True

def df_to_html(df,index=False):
    html=html=o.to_html(table_id='ids',index=index)#
    css="""<style type="text/css">
            table.dataframe {font-size:12px;color:#333333;width:98%;border-width: 1px;border-color: #9dcc7a;border-collapse: collapse;}
            table.dataframe th {font-size:13px;background-color:#abd28e; padding: 6px;  text-align:center;}
            table.dataframe tr {background-color:#ffffff;}
            table.dataframe td {font-size:12px; text-align: right; padding: 4px;  }
            </style>\n"""
    css=css+html
    return css

def send_mail(sub,content,filepath='',content_type='plain',to_list=['xuzh@chyjr.com'],imglist=None):
    mail_host="smtp.163.com"            #使用的邮箱的smtp服务器地址
    mail_user="443435766@163.com"      #用户名
    mail_pass="hua816813"       #密码
#    mail_host="mail.chyjr.com"            #使用的邮箱的smtp服务器地址
#    mail_user="dps@chyjr.com"      #用户名
#    mail_pass="Hjk123456Hjk123456"       #密码
    msg = multipart.MIMEMultipart()  
    msg['Subject'] = sub #邮件标题
    msg['From'] = mail_user
    msg['To'] = ";".join(to_list)   #将收件人列表以‘；’分隔
    if os.path.exists(filepath):
        fname = "attachment; filename="+os.path.basename(filepath)
        #print(fname)
        fs=open(filepath, 'r',encoding='utf-8')
        logs=fs.read()
        fs.close()
        att =MIMEText(logs, 'plain', 'utf-8')
        att["Content-Type"] = 'application/octet-stream'  
        att["Content-Disposition"] = fname
        msg.attach(att)
        content=content+'\n\n\n'+logs
        #print(content)
    if imglist and isinstance(imglist,list) and content_type=='html':
        tp = '<b>以下包含图片:</b> <br>'
        for index in range(len(imglist)):
            if index % 2 == 0:
                tp += '<img src="cid:'+str(index)+'"><br>'
            else:
                tp += '<img src="cid:' + str(index) + '">'
        content=content+'\n'+tp
    text_con = MIMEText(content,content_type,"utf-8") #plain html
    msg.attach(text_con)
    if imglist and isinstance(imglist,list) and content_type=='html':
        from email.mime.image import MIMEImage
        for index in range(len(imglist)):
            fp = open(imglist[index], 'rb')
            msgImage = MIMEImage(fp.read())
            fp.close()
            msgImage.add_header('Content-ID', '<'+str(index)+'>')
            msg.attach(msgImage)
    try:
        server = smtplib.SMTP()
        server.connect(mail_host)     #连接服务器
        server.login(mail_user,mail_pass)    #登录操作
        server.sendmail(mail_user, to_list, msg.as_string())
        print('邮件发送成功')
        server.close()
        return True
    except Exception as e:
        print ('邮件发送失败：',str(e))
        return False
if __name__ == '__main__':  
    diff_days=10
    rdate =  datetime.datetime.now() - datetime.timedelta(days=diff_days)
    start_dt= rdate.strftime('%Y-%m-%d') # %Y-%m-%d %H:%M:%S
    dbpath='/home/xzh/data/stockdata2018.db'
    engine= create_engine('sqlite:////'+dbpath)
    sql="select date,tag,count(1) cnt from stocks  where date>='{start_dt}' and code not in('sz','sh')  group by tag,date"
    sql="select date,tag,count(1) cnt from stocks  where date>='2019-01-01' and date<='2019-03-30' and code not in('sz','sh')  group by tag,date"
    o= pd.read_sql(sql.format(start_dt=start_dt),con= engine)
    o['date']=o['date'].apply(lambda x:x.replace('-',''))
    o=pd.pivot_table(o,values='cnt',index='tag',columns='date',aggfunc=['max','sum'],fill_value=0)
    o.columns.names=['func','date']
    o=o.swaplevel('func','date',axis=1)
    o.sort_index(axis=1,level=0,inplace=True)
    for i in o.columns.levels[0]:
        o[(i,'max')]=o[(i,'max')]*100/o[(i,'sum')].sum()
        o[(i,'max')]=o[(i,'max')].apply(lambda x:round(x,2))
    print("data rows:",o.shape)
    
    if o.shape[0]>0:
        o.rename(columns={'max':'占比','sum':'数量'},inplace=True)
        pt=o.loc[:,(slice(None),'占比')].copy(True)
        pt.columns=pt.columns.levels[0]
        pt.reset_index(inplace=True)
        pt['tag']=pt['tag'].apply(lambda x: 6*x/abs(x) if abs(x)>6 else x)
        pt=pt.groupby('tag').sum()
        plt.rcParams['font.sans-serif'] = ['SimHei']
        plt.rcParams['axes.unicode_minus'] = True
        fig=plt.figure(figsize=(14,7))
        ax=fig.add_subplot(1,1,1)
        #pt.sort_index(ascending=False,inplace=True)
        st=pt[pt.index<=-1].sum()
        st.plot(c='darkgreen',ax=ax,rot=30,legend=False)
        plt.annotate('下跌占比', xy=(1, st[1]), xytext=(2, st[1]+5),arrowprops=dict(facecolor='m', shrink=0.01))
        st=pt[pt.index<=-3].sum()
        st.plot(c='b',ax=ax,rot=30,legend=False)
        plt.annotate('跌幅超2%', xy=(3, st[3]), xytext=(4, st[3]+5),arrowprops=dict(facecolor='m', shrink=0.01))
        st=pt[pt.index>=2].sum()
        st.plot(c='r',ax=ax,rot=30,legend=False)
        plt.annotate('涨幅超2%', xy=(6, st[6]), xytext=(7, st[6]+5),arrowprops=dict(facecolor='m', shrink=0.01))
        pt.T.plot(kind='bar',stacked=True,ax=ax,rot=30,sort_columns=True, cmap='summer',legend=False,grid=True)#alpha=0.7
        #plt.title('Interesting Graph',color='blue') #设置字体颜色
        plt.xlabel(None) # delete x lable name show 
        plt.savefig('temp.svg',bbox_inches = 'tight')
        #imglist=['temp.png','temp01.png']
        html=df_to_html(o,True)
        #send_mail("大盘监控",html,content_type='html',imglist=imglist)
#        for i in imglist:
#            os.remove(i)
  
